﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using Common;

using System.IO;
using System.Data.OleDb;

namespace DAL
{
    public class KaoQin
    {
        //
        public Dat_KaoQin SelectKaoQin(int KqKey)
        {
            CIMDataContext db = new CIMDataContext();
            return db.Dat_KaoQin.Single(k => k.KqKey == KqKey);
        }


        //添加考勤
        public bool AddKaoQin(Dat_KaoQin kaoqin)
        {
            bool Success;
            CIMDataContext db = new CIMDataContext();

            db.Dat_KaoQin.InsertOnSubmit(kaoqin);
            try
            {
                db.SubmitChanges();
                Success = true;
            }
            catch (Exception ex)
            {
                WebUtility.Print(ex.Message);
                Success = false;
            }
            return Success;
        }

        //修改考勤
        public bool EditKaoQin(Dat_KaoQin kaoqin)
        {
            CIMDataContext db = new CIMDataContext();

            Dat_KaoQin KQ = db.Dat_KaoQin.Single(k => k.KqKey == kaoqin.KqKey);
            KQ.KqType = kaoqin.KqType;
            KQ.MUserKey = kaoqin.MUserKey;
            KQ.DanNum = kaoqin.DanNum;
            KQ.StartTime = kaoqin.StartTime;
            KQ.EndTime = kaoqin.EndTime;
            KQ.Reason = kaoqin.Reason;
            KQ.SpanTimeD = kaoqin.SpanTimeD;
            KQ.SpanTimeH = kaoqin.SpanTimeH;
            KQ.SpanTimeM = kaoqin.SpanTimeM;

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public void DelKaoQin(int KqKey)
        {
            CIMDataContext db = new CIMDataContext();

            //对应预约单号考勤恢复正常
            string DanNum = new KaoQin().SelectKaoQin(KqKey).DanNum;

            if (!string.IsNullOrEmpty(DanNum))
            {
                new News().ResetKaoQin(DanNum);
            }

            Dat_KaoQin kaoqin = db.Dat_KaoQin.Single(k => k.KqKey == KqKey);
            db.Dat_KaoQin.DeleteOnSubmit(kaoqin);
            db.SubmitChanges();
        }


        //条件查询
        public IQueryable<Dat_KaoQin> SelectKaoQin(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_KaoQin> Query;

            if (QueryTable == null)
            {
                Query = from k in db.Dat_KaoQin
                        orderby k.KqKey descending
                        select k;
            }
            else
            {
                Query = from k in GetConditionQuery(QueryTable)
                        orderby k.KqKey descending
                        select k;
            }
            return Query;
        }

        private IQueryable<Dat_KaoQin> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            var predicate = PredicateBuilder.True<Dat_KaoQin>();

            if (QueryTable["MUserKey"] != null)
                predicate = predicate.And(k => k.MUserKey == Convert.ToInt32(QueryTable["MUserKey"]));
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And(k => k.KqType.Contains(QueryTable["Keyword"].ToString().Trim()) || k.Reason.Contains(QueryTable["Keyword"].ToString().Trim()));
            if (QueryTable["OneDate"] != null)
            {
                try
                {
                    DateTime OneDate = Convert.ToDateTime(QueryTable["OneDate"]);
                    predicate = predicate.And(k => k.StartTime  >= OneDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["TwoDate"] != null)
            {
                try
                {
                    DateTime TwoDate = Convert.ToDateTime(QueryTable["TwoDate"].ToString().Split(' ')[0] + " 23:59:59");
                    predicate = predicate.And(k => k.EndTime <= TwoDate);
                }
                catch (Exception ex) { }
            }
           
            return db.Dat_KaoQin.Where(predicate);
        }

        //Export
        public static void DataToExcel(Hashtable QueryTable)
        {
            IQueryable<Dat_KaoQin> Query = new KaoQin().SelectKaoQin(QueryTable);

            WebUtility.DelExport();

            string FileName = "~/export/" + Guid.NewGuid().ToString() + ".xls";
            string sNewFullFile = HttpContext.Current.Server.MapPath(FileName);

            try
            {
                File.Copy(HttpContext.Current.Server.MapPath("~/common/template.xls"), sNewFullFile);
            }
            catch (Exception er)
            {
                HttpContext.Current.Response.Write(er.Message);
                return;
            }

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbCommand cmd = null;

            bool bRet = false;

            try
            {
                conn.Open();
                cmd = new OleDbCommand("create table [考勤信息]([姓名] Text,[类型] Text,[开始时间] Text,[结束时间] Text,[时长] Text,[事由] Text)", conn);
                cmd.ExecuteNonQuery();


                string strSQL = "INSERT INTO [考勤信息$] ([姓名], [类型],[开始时间],[结束时间],[时长],[事由]) VALUES (?,?,?,?,?,?)";
                cmd = new OleDbCommand(strSQL, conn);
                for (int i = 0; i < 6; i++)
                {
                    cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
                }
                foreach (Dat_KaoQin kaoqin in Query)
                {
                    cmd.Parameters[0].Value = kaoqin.MUserKey == null ? "" : new Account().GetAccountName(kaoqin.MUserKey);
                    cmd.Parameters[1].Value = string.IsNullOrEmpty(kaoqin.KqType) ? "" : kaoqin.KqType;
                    cmd.Parameters[2].Value = kaoqin.StartTime == null ? "" : kaoqin.StartTime.ToString();
                    cmd.Parameters[3].Value = kaoqin.EndTime == null ? "" : kaoqin.EndTime.ToString();
                    cmd.Parameters[4].Value = kaoqin.SpanTimeD + "天" + kaoqin.SpanTimeH + "时" + kaoqin.SpanTimeM;
                    cmd.Parameters[5].Value = string.IsNullOrEmpty(kaoqin.Reason) ? "" : kaoqin.Reason;

                    cmd.ExecuteNonQuery();
                }
                bRet = true;
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);

            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Dispose();
            }

            if (bRet)
                HttpContext.Current.Response.Redirect(FileName);
        }
    }
}
